﻿<?xml version="1.0" encoding="utf-8" ?>
<root>
  <table name="kp_DriveInfo">
    <column name="ServerName" datatype="varchar(50)" />
    <column name="dtmStamp" datatype="datetime" />
    <column name="Drive" datatype="varchar(50)" />
    <column name="Label" datatype="varchar(50)" />
    <column name="[Total(MB)]" datatype="numeric" />
    <column name="[Free(MB)]" datatype ="numeric" />
    <column name="GUID" datatype="varchar(50)" />
  </table>
  <sql>
    <![CDATA[
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576
declare @label varchar(200)
CREATE TABLE #drives 
	(Drive char(1) PRIMARY KEY,
	FreeSpace int NULL,		
	TotalSize int NULL,
	Label varchar(200)) 
INSERT #drives(Drive,FreeSpace) 
EXEC master.dbo.xp_fixeddrives 

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @fso OUT 
IF @hr <> 0 
	EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT Drive 
	from #drives 
	ORDER by Drive
OPEN dcur 
	FETCH NEXT FROM dcur INTO @drive
		WHILE @@FETCH_STATUS=0
			BEGIN
				EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
				IF @hr <> 0 
					EXEC sp_OAGetErrorInfo @fso 
				EXEC @hr =sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT 
				IF @hr <> 0 
					EXEC sp_OAGetErrorInfo @odrive 
				EXEC @hr =sp_OAGetProperty @odrive,'VolumeName', @label OUT 
				IF @hr <> 0 
					EXEC sp_OAGetErrorInfo @odrive 
				UPDATE #drives 
					SET TotalSize=@TotalSize/@MB 
					, Label = @label
					WHERE Drive=@drive 
				FETCH NEXT FROM dcur INTO @drive
			End
Close dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso 
IF @hr <> 0 
	EXEC sp_OAGetErrorInfo @fso
SELECT @@SERVERNAME as ServerName, getdate() as dtmStamp, Drive, Label, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives
ORDER BY Drive
DROP TABLE #drives
    ]]>
  </sql>
  
</root>
